﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Structure.Interfaces;
using Structure.Objects;

namespace Storage
{
    public class DBAccess : iStorage
    {
        private SqlConnection _connection;
        private iSqlConnect _connect;
        private iExecute _execute;

        public DBAccess()
        {
            _connect = BuildIConnect.Build();
            _execute = BuildIExecute.Build();
            _connection = _connect.OpenConnection();
        }

        public DataTable ProjectList()
        {
            string sql = "SELECT id_project,name, id_user, cast (startdate as char) as startdate,  cast (enddate as char) as enddate  FROM Project";
            DataTable dt = new DataTable();
            dt = _execute.ExecuteQuery(sql, _connection);

            // заменяем подписи столбов
            dt.Columns["id_project"].ColumnName = "#";
            dt.Columns["name"].ColumnName = "Наименование";
            dt.Columns["id_user"].ColumnName = "Менеджер проекта";
            dt.Columns["startdate"].ColumnName = "Дата начала";
            dt.Columns["enddate"].ColumnName = "Дата окончания";

            return dt;
        }

        public void AddProject(Project p)
        {
            string sql = String.Format("INSERT INTO Project (name, id_user, startdate, enddate) VALUES ('{0}', '{1}' , CAST ('{2}' as datetime) , CAST  ('{3}' as datetime))", p.Name, p.ManagerId, p.StartDate, p.EndDate);
            _execute.ExecuteNonQuery(sql, _connection);
        }
        public void UpdateProject(string id_project, Project p)
        {
            string sql = String.Format("UPDATE Project SET name = '{0}', id_user='{1}', startdate = '{2}',  enddate = '{3}' WHERE  id_project = '{4}' ", p.Name, p.ManagerId, p.StartDate, p.EndDate, id_project);
            _execute.ExecuteNonQuery(sql, _connection);
        }

        public void DeleteProject(string id_project)
        {
            string sql = String.Format("DELETE FROM Project WHERE id_project = '{0}'", id_project);
            _execute.ExecuteNonQuery(sql, _connection);
        }

        public DataTable SelectProject(int id_project)
        {
            string sql = String.Format("SELECT id_project, name , id_user, cast (startdate as char) as startdate,  cast (enddate as char) as enddate  FROM Project WHERE id_project = '{0}'", id_project);
            DataTable dt = new DataTable();
            dt = _execute.ExecuteQuery(sql, _connection);

            // заменяем подписи столбов
            dt.Columns["id_project"].ColumnName = "#";
            dt.Columns["name"].ColumnName = "Наименование";
            dt.Columns["id_user"].ColumnName = "Менеджер проекта";
            dt.Columns["startdate"].ColumnName = "Дата начала";
            dt.Columns["enddate"].ColumnName = "Дата окончания";

            return dt;
        }

        public Project GetProject(string id_project)
        {
            string sql = String.Format("Select name , id_user, cast (startdate as char) as startdate,  cast (enddate as char) as enddate From Project Where id_project = '{0}'", id_project);
            DataTable dt = new DataTable();
            dt = _execute.ExecuteQuery(sql, _connection);
            DataRow row = dt.Rows[0];
            Project p = new Project(row["name"].ToString(), 0, row["startdate"].ToString(), row["enddate"].ToString());
            return p;
        }
        //(SELECT fio FROM Users u WHERE u.id_user = t.id_user) user
        //  dt.Columns["user"].ColumnName = "Исполнитель";
        public DataTable TaskList()
        {
            string sql = "SELECT id_task, name, description, (SELECT name FROM Project p WHERE p.id_project = t.id_project) name_project,  (SELECT name FROM Status s WHERE s.id_status = t.status) status, cast (startdate as char) as startdate,  cast (enddate as char) as enddate, (SELECT login FROM Users u WHERE u.id_user = t.id_user) id_user FROM Task t";
           
            DataTable dt = new DataTable();
            dt = _execute.ExecuteQuery(sql, _connection);

             //заменяем подписи столбов
            dt.Columns["id_task"].ColumnName = "#";
            dt.Columns["name"].ColumnName = "Наименование";            
            dt.Columns["description"].ColumnName = "Описание";
            dt.Columns["name_project"].ColumnName = "Проект";
            dt.Columns["status"].ColumnName = "Статус";
            dt.Columns["startdate"].ColumnName = "Дата начала";
            dt.Columns["enddate"].ColumnName = "Дата окончания";
            dt.Columns["id_user"].ColumnName = "Исполнитель";

            return dt;
        }

        public void AddTask(Task t)
        {
            string sql = String.Format("INSERT INTO Task (name, description, id_project, status, startdate, enddate, id_user) VALUES ('{0}', '{1}' , '{2}', '{3}', CAST ('{4}' as datetime) , CAST  ('{5}' as datetime), '{6}')", t.Name, t.Description, t.IdProject, t.Status, t.StartDate, t.EndDate, t.IdUser);
            _execute.ExecuteNonQuery(sql, _connection);          
        }

        public void DeleteTask(string id_task)
        {
            string sql = String.Format("DELETE FROM Task WHERE id_task = '{0}'", id_task);          
            _execute.ExecuteNonQuery(sql, _connection);
        }

        public DataTable SelectTask(int id_task)
        {
            string sql = String.Format("SELECT id_task, name, description, (SELECT name FROM Project p WHERE p.id_project = t.id_project) id_project, (SELECT name FROM Status s WHERE s.id_status = t.status) status, cast (startdate as char) as startdate,  cast (enddate as char) as enddate, (SELECT login FROM Users u WHERE u.id_user = t.id_user) id_user  FROM Task t WHERE id_task = '{0}'", id_task);
            DataTable dt = new DataTable();
            dt = _execute.ExecuteQuery(sql, _connection);

            // заменяем подписи столбов
            dt.Columns["id_task"].ColumnName = "#";
            dt.Columns["name"].ColumnName = "Наименование";
            dt.Columns["description"].ColumnName = "Описание";
            dt.Columns["id_project"].ColumnName = "Проект";          
            dt.Columns["status"].ColumnName = "Статус";
            dt.Columns["startdate"].ColumnName = "Дата начала";
            dt.Columns["enddate"].ColumnName = "Дата окончания";
            dt.Columns["id_user"].ColumnName = "Исполнитель";

            return dt;
        }

        public Task GetTask(string id_task)
        {
            string sql = String.Format("Select name, description, id_project, status, cast (startdate as char) as startdate,  cast (enddate as char) as enddate, id_user From Task WHERE id_task = '{0}'", id_task);
            DataTable dt = new DataTable();
            dt = _execute.ExecuteQuery(sql, _connection);
            DataRow row = dt.Rows[0];
            Task t = new Task(row["name"].ToString(), Convert.ToInt32(row["id_project"]), row["description"].ToString(), Convert.ToInt32(row["status"]), row["startdate"].ToString(), row["enddate"].ToString(), Convert.ToInt32(row["id_user"]));
            return t;
        }

        public void UpdateTask(string id_task, Task t)
        {
            string sql = String.Format("UPDATE Task SET name = '{0}', id_user='{1}', description ='{2}', id_project = '{3}', status = '{4}', startdate = '{5}',  enddate = '{6}' WHERE  id_task = '{7}' ", t.Name, t.IdUser, t.Description, t.IdProject, t.Status, t.StartDate, t.EndDate, id_task);
            _execute.ExecuteNonQuery(sql, _connection);
        }

        public void DeleteUser(string index)
        {
            string sql = String.Format("DELETE FROM Users WHERE id_user = '{0}'", index);
            _execute.ExecuteNonQuery(sql, _connection);
        }

        public void AddUser(User u)
        {            
            string sql = String.Format("INSERT INTO Users (login, password, fio, role) VALUES ('{0}', '{1}', '{2}', '{3}')", u.Login, u.Password, u.Credentials, Convert.ToInt32(u.Role));
            _execute.ExecuteNonQuery(sql, _connection);
        }

        // (SELECT role_name FROM Role r WHERE r.id_role = u.role)
        public DataTable UserList()
        {

            string sql = "SELECT id_user, login, password, fio, (SELECT role_name FROM Role r WHERE r.id_role = u.role) role FROM Users u";

            DataTable dt = new DataTable();
            dt = _execute.ExecuteQuery(sql, _connection);

            //заменяем подписи столбов
            dt.Columns["id_user"].ColumnName = "#"; 
            dt.Columns["password"].ColumnName = "Пароль";
            dt.Columns["login"].ColumnName = "Псевдоним";
            dt.Columns["fio"].ColumnName = "Полное имя";
            dt.Columns["role"].ColumnName = "Статус";           

            return dt;
        }

        public string Login(string pLogin, string pPass, ref string pFio)
        {
            string sql = String.Format("SELECT * FROM Users WHERE login = '{0}' AND password = '{1}'", pLogin, pPass);
            DataTable dt = new DataTable();
            dt = _execute.ExecuteQuery(sql, _connection);
            string role;
            try
            {
                role = dt.Rows[0]["role"].ToString();
                pFio = dt.Rows[0]["fio"].ToString();
            }
            catch (System.Exception ex)
            {
                return role = "0";
            }
            //role.
            return role;
        }

        public void AddUserInProject(string id_user, string id_project)
        {
            string sql = String.Format("INSERT into UserInProject values '{0}', '{1}'", id_project, id_user);
            _execute.ExecuteNonQuery(sql, _connection);
        }

        public void DeleteUserFromProject(string id_user, string id_project)
        {
            string sql = String.Format("Delete from UserInProject where id_user='{0}' AND id_project = '{1}'", id_user, id_project);
            _execute.ExecuteNonQuery(sql, _connection);
        }

        public DataTable GetAllUsersInProjects(string id_project)
        {
            string sql = String.Format("Select * From UserInProject where id_project='{0}'", id_project);
            return _execute.ExecuteQuery(sql, _connection);
        }

        public DataTable StatusList()
        {
            string sql = "SELECT name, id_status FROM Status";
            DataTable dt = new DataTable();

            _execute.ExecuteQuery(sql, _connection);

            dt.Columns["name"].ColumnName = "Наименование";
            dt.Columns["id_status"].ColumnName = "#";

            return dt;
        }

    }
}
